package com.otitan.forest.right.util;

import cn.hutool.core.util.StrUtil;
import com.otitan.forest.right.constant.StaticConstant;
import com.otitan.forest.right.entity.BssForestRightInfoEntity;
import com.otitan.forest.right.exception.CommonException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.io.FileInputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.*;


/**
 * @Description: java类作用描述
 * @Author: jiachangsen
 * @CreateDate: 2020/08/11 18:08
 * @UpdateUser: jiachangsen
 * @UpdateDate: 2020/08/11 18:08
 * @UpdateRemark: 修改内容
 * @Version: 1.0
 */
@Slf4j
public class ExcelReader {


//    /**
//     * 省级
//     */
//    private Map<String, String> provinceMap;

    /**
     * 市级
     */
    private Map<String, String> cityMap;

    /**
     * 县级
     */
    private Map<String, String> countyMap;
    /**
     * 县级
     */
    private Map<String, String> countyParentIdMap;

    /**
     * 宗地号
     */
    private Set<String> allGroundNoSet;

    /**
     * 乡镇
     */
    private Map<String, String> villageMap;


    public ExcelReader(Map<String, String> cityMap,
                       Map<String, String> countyMap,
                       Map<String, String> countyParentIdMap,
                       Map<String, String> villageMap,
                       Set<String> allGroundNoSet) {
//        this.provinceMap = provinceMap;
        this.cityMap = cityMap;
        this.countyMap = countyMap;
        this.countyParentIdMap = countyParentIdMap;
        this.villageMap = villageMap;
        this.allGroundNoSet = allGroundNoSet;
    }

    public ExcelReader(Map<String, String> cityMap,
                       Map<String, String> countyMap,
                       Map<String, String> countyParentIdMap,
                       Map<String, String> villageMap
    ) {
//        this.provinceMap = provinceMap;
        this.cityMap = cityMap;
        this.countyMap = countyMap;
        this.countyParentIdMap = countyParentIdMap;
        this.villageMap = villageMap;
    }


    /**
     * 读取Excel文件内容
     *
     * @param fileName 要读取的Excel文件所在路径
     * @return 读取结果列表，读取失败时返回null
     */
    public Map<String, Object> readExcel(String fileName) {

        Workbook workbook = null;
        FileInputStream inputStream = null;

        try {
            // 获取Excel后缀名
            String fileType = fileName.substring(fileName.lastIndexOf(".") + 1);
            // 获取Excel文件
            File excelFile = new File(fileName);
            if (!excelFile.exists()) {
                log.debug("指定的Excel文件不存在！");
                return null;
            }

            // 获取Excel工作簿
            inputStream = new FileInputStream(excelFile);
            workbook = MyExcelUtil.getWorkbook(inputStream, fileType);

            // 读取excel中的数据
            return parseExcel(workbook);
        } catch (Exception e) {
            log.debug("解析Excel失败，文件名：" + fileName + " 错误信息：" + e.getMessage());
            return null;
        } finally {
            try {
                if (null != workbook) {
                    workbook.close();
                }
                if (null != inputStream) {
                    inputStream.close();
                }
            } catch (Exception e) {
                log.debug("关闭数据流出错！错误信息：" + e.getMessage());
                return null;
            }
        }
    }

    /**
     * 解析Excel数据
     *
     * @param workbook Excel工作簿对象
     * @return 解析结果
     */
    private Map<String, Object> parseExcel(Workbook workbook) {
        List<BssForestRightInfoEntity> resultDataList = new ArrayList<>();
        List<Row> resultErrorDataList = new ArrayList<>();
        // 解析sheet
        for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) {
            Sheet sheet = workbook.getSheetAt(sheetNum);

            // 校验sheet是否合法
            if (sheet == null) {
                continue;
            }

            // 获取第一行数据
            int firstRowNum = sheet.getFirstRowNum();
            Row firstRow = sheet.getRow(firstRowNum);
            if (null == firstRow) {
                log.debug("解析Excel失败，在第一行没有读取到任何数据！");
            }

            // 解析每一行的数据，构造数据对象
            int rowStart = firstRowNum + 3;
            int rowEnd = sheet.getLastRowNum();
            for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
                Row row = sheet.getRow(rowNum);
                if (null == row) {
                    continue;
                }
                BssForestRightInfoEntity dto = null;
                Object o = convertRowToData(row);
                if (o instanceof BssForestRightInfoEntity) {
                    dto = (BssForestRightInfoEntity) o;
                } else {
                    resultErrorDataList.add((Row) o);
                }
                if (dto != null) {
                    resultDataList.add(dto);
                }
            }
        }
        Map<String, Object> resMap = new HashMap<>();
        resMap.put("data", resultDataList);
        resMap.put("errorData", resultErrorDataList);
        return resMap;
    }

    /**
     * 将单元格内容转换为字符串
     *
     * @param cell
     * @return
     */
    private String convertCellValueToString(Cell cell) {
        if (cell == null) {
            return null;
        }
        String returnValue = null;
        switch (cell.getCellType()) {
            case NUMERIC:
                //数字
                Double doubleValue = cell.getNumericCellValue();
                // 格式化科学计数法，取一位整数
                DecimalFormat df = new DecimalFormat("0");
                returnValue = df.format(doubleValue);
                break;
            case STRING:
                //字符串
                returnValue = cell.getStringCellValue();
                break;
            case BOOLEAN:
                //布尔
                Boolean booleanValue = cell.getBooleanCellValue();
                returnValue = booleanValue.toString();
                break;
            case BLANK:
                // 空值
                break;
            case FORMULA:
                // 公式
                returnValue = cell.getCellFormula();
                break;
            case ERROR:
                // 故障
                break;
            default:
                break;
        }
        if (StrUtil.isNotEmpty(returnValue)) {
            returnValue = returnValue.trim();
        }

        return returnValue;
    }

    /**
     * 提取每一行中需要的数据，构造成为一个结果数据对象
     * <p>
     * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
     *
     * @param row 行数据
     * @return 解析后的行数据对象，行数据错误时返回null
     */
    private Object convertRowToData(Row row) {

        BssForestRightInfoEntity resultData = new BssForestRightInfoEntity();

        int cellNum = 0;
        // 宗地号
        String groundNo = convertCellValueToString(row.getCell(cellNum++));
        resultData.setGroundNo(groundNo);
//        if (StrUtil.isNotEmpty(groundNo)) {
            /*if (allGroundNoSet.contains(groundNo)){
                createErrorCell(row, "第" + cellNum + "列错误，宗地号不能重复。");
                return row;
            }  */
//            allGroundNoSet.add(groundNo);
       /* } else {
            createErrorCell(row, "第" + cellNum + "列错误，不能为空！");
            return row;
        }*/


        // 林权证号
        resultData.setForestRightNo(convertCellValueToString(row.getCell(cellNum++)));

        // 不动产单元号
        resultData.setRealUnitNo(convertCellValueToString(row.getCell(cellNum++)));

        // 县
        String countyName = convertCellValueToString(row.getCell(cellNum++));
        if (StrUtil.isNotEmpty(countyName)) {
            String countyId = countyMap.get(countyName);
            if (StrUtil.isNotEmpty(countyId)) {
                String cityId = countyParentIdMap.get(countyId);
                String provinceId = cityMap.get(cityId);
                resultData.setCounty(countyId);
                resultData.setCity(cityId);
                resultData.setProvince(provinceId);
            } else {
                createErrorCell(row, "第" + cellNum + "列错误，请检查县名是否正确，请输入完整的县名。");
                return row;
            }
        } else {
            createErrorCell(row, "第" + cellNum + "列错误，县不能为空");
            return row;
        }

        // 乡/镇/街道
        String townName = convertCellValueToString(row.getCell(cellNum++));

        if (StrUtil.isNotEmpty(townName)) {
            String townId = villageMap.get(townName + resultData.getCounty());
            if (StrUtil.isNotEmpty(townId)) {
                resultData.setTown(townId);
            } else {
                createErrorCell(row, "第" + cellNum + "列错误，请检查乡/镇/街道名是否正确，请输入完整的乡/镇/街道名");
                return row;
            }
        }

        // 村
        resultData.setVillage(convertCellValueToString(row.getCell(cellNum++)));
        // 组
        resultData.setGroupName(convertCellValueToString(row.getCell(cellNum++)));
        // 林地所有权权利人
        resultData.setLandBelongUser(convertCellValueToString(row.getCell(cellNum++)));
        // 林地所有权权利人统一社会信用代码
        resultData.setLandBelongUserCreditCode(convertCellValueToString(row.getCell(cellNum++)));
        // 林地使用权（自留山）权利人
        resultData.setLandUseUser(convertCellValueToString(row.getCell(cellNum++)));
        // 林地使用权（自留山）权利人身份证号码
        resultData.setLandUseUserIdNo(convertCellValueToString(row.getCell(cellNum++)));

        // 林地使用权（自留山）起始日期
        try {
            Date date = cellStrToDate(convertCellValueToString(row.getCell(cellNum++)));
            resultData.setLandUseStartDate(date);
        } catch (CommonException e) {
            // e.printStackTrace();
            createErrorCell(row, "第" + cellNum + "列错误，" + e.getMessage());
            return row;
        }

        // 林地承包经营权权利人
        resultData.setLandManageUser(convertCellValueToString(row.getCell(cellNum++)));
        // 林地承包经营权权利人身份证号码
        resultData.setLandManageUserIdNo(convertCellValueToString(row.getCell(cellNum++)));
        // 林地承包经营权权利人统一社会信用代码
        resultData.setLandManageUserCreditCode(convertCellValueToString(row.getCell(cellNum++)));

        // 林地承包经营权起始日期
        try {
            Date date = cellStrToDate(convertCellValueToString(row.getCell(cellNum++)));
            resultData.setLandManageStartDate(date);
        } catch (CommonException e) {
            // e.printStackTrace();
            createErrorCell(row, "第" + cellNum + "列错误，" + e.getMessage());
            return row;
        }
        // 林地承包经营权终止日期
        try {
            Date date = cellStrToDate(convertCellValueToString(row.getCell(cellNum++)));
            resultData.setLandManageEndDate(date);
        } catch (CommonException e) {
            // e.printStackTrace();
            createErrorCell(row, "第" + cellNum + "列错误，" + e.getMessage());
            return row;
        }

        // 林木所有权权利人
        resultData.setTreesBelongUser(convertCellValueToString(row.getCell(cellNum++)));
        // 林木所有权权利人身份证号码
        resultData.setTreesBelongUserIdNo(convertCellValueToString(row.getCell(cellNum++)));
        // 林木所有权权利人统一社会信用代码
        resultData.setTreesBelongUserCreditCode(convertCellValueToString(row.getCell(cellNum++)));
        // 林木使用权权利人
        resultData.setTreesUseUser(convertCellValueToString(row.getCell(cellNum++)));
        // 林木使用权权利人身份证号码
        resultData.setTreesUseIdNo(convertCellValueToString(row.getCell(cellNum++)));
        // 林木使用权权利人统一社会信用代码
        resultData.setTreesUseCreditCode(convertCellValueToString(row.getCell(cellNum++)));
        // 林班
        resultData.setCompartment(convertCellValueToString(row.getCell(cellNum++)));
        // 小班
        resultData.setSubCompartment(convertCellValueToString(row.getCell(cellNum++)));
        // 面积
        try {
            BigDecimal area = cellStrToBigDecimal(convertCellValueToString(row.getCell(cellNum++)));
            resultData.setArea(area);
        } catch (CommonException e) {
//            e.printStackTrace();
            createErrorCell(row, "第" + cellNum + "列错误，" + e.getMessage());
            return row;
        }
        // 株数
        try {
            Integer num = cellStrToInteger(convertCellValueToString(row.getCell(cellNum++)));
            resultData.setNum(num);
        } catch (CommonException e) {
//            e.printStackTrace();
            createErrorCell(row, "第" + cellNum + "列错误，" + e.getMessage());
            return row;
        }
        // 主要树种
        resultData.setTreeSpecies(convertCellValueToString(row.getCell(cellNum++)));
        // 林种
        resultData.setForestCategory(convertCellValueToString(row.getCell(cellNum++)));
        // 注记
        resultData.setAnnotation(convertCellValueToString(row.getCell(cellNum++)));
        // 小地名
        resultData.setSubPlaceName(convertCellValueToString(row.getCell(cellNum++)));
        // 东
        resultData.setBorderEast(convertCellValueToString(row.getCell(cellNum++)));
        // 西
        resultData.setBorderWest(convertCellValueToString(row.getCell(cellNum++)));
        // 南
        resultData.setBorderSouth(convertCellValueToString(row.getCell(cellNum++)));
        // 北
        resultData.setBorderNorth(convertCellValueToString(row.getCell(cellNum++)));
        // 备注
        resultData.setRemark(convertCellValueToString(row.getCell(cellNum++)));
        // 状态
        resultData.setStatus(0);
        //是否关联图形，（0 未关联，1 已关联）
        resultData.setHaveGis(StaticConstant.NOT_HAVE_GIS);

        return resultData;
    }

    /**
     * string 转 int
     *
     * @param [str]
     * @return java.lang.Integer
     * @throws
     * @author jiachangsen
     * @date 2020/08/12 14:00
     */
    private Integer cellStrToInteger(String str) throws CommonException {
        if (str == null) {
            return null;
        }
        try {
            return Integer.parseInt(str);
        } catch (NumberFormatException e) {
//            e.printStackTrace();
            log.error("【Excel读取】数字格式错误，必须为整数。");
            throw new CommonException("数字格式错误，必须为整数。");
        }
    }

    /**
     * string 转 BigDecimal
     *
     * @param [str]
     * @return java.lang.Integer
     * @throws
     * @author jiachangsen
     * @date 2020/08/12 14:00
     */
    private BigDecimal cellStrToBigDecimal(String str) throws CommonException {
        if (str == null) {
            return null;
        }
        try {
            return new BigDecimal(str);
        } catch (NumberFormatException e) {
//            e.printStackTrace();
            log.error("【Excel读取】数字格式错误，可输入整数或小数。");
            throw new CommonException("数字格式错误，可输入整数或小数。");
        }
    }

    /**
     * string 转 Date
     *
     * @param [str]
     * @return java.lang.Integer
     * @throws
     * @author jiachangsen
     * @date 2020/08/12 14:00
     */
    private Date cellStrToDate(String str) throws CommonException {
        if (str == null || "".equals(str)) {
            return null;
        }

        try {
            Date date = DateUtil.strToDate1(str, DateUtil.YYYYMMDD1);
            return date;
        } catch (ParseException e) {
//            e.printStackTrace();
            log.error("日期格式错误，正确格式：yyyy.MM.dd，例如：2020.01.01");
            throw new CommonException("日期格式错误，正确格式：yyyy.MM.dd，例如：2020.01.01");
        }
    }

    /**
     * 设置错误消息
     *
     * @param [row, errorMsg]
     * @return void
     * @throws
     * @author jiachangsen
     * @date 2020/08/12 10:44
     */
    private void createErrorCell(Row row, String errorMsg) {
        Cell cell = row.createCell(36);
        cell.setCellValue(errorMsg);
    }


}
